﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Collections.Specialized;
using MySql.Data;
using MySql.Data.MySqlClient;
using System.Data;
using Accountability4net.MySQLDbProvider.Properties;
using Accountability4net.MySQLDbProvider;
using Accountability4net.Contact;

namespace Accountability4net.MySQLDbProvider
{
    public class MySQLDbProvider : MonengineProviderBase
    {
        private const string CONNECTION_STRING_KEY = "ConnectionString";
        private const string INSTALL_CONNECTION_STRING_KEY = "InstallConnectionString";
        private const string INSTALL_KEY = "Install";
        private string connectionString_;
        private string installConnectionString_;
        public MySQLDbProvider()
        {
        }

        public override void Initialize(string name, NameValueCollection config)
        {
            base.Initialize(name, config);
            connectionString_ = config[CONNECTION_STRING_KEY];
            var install = config[INSTALL_KEY];
            bool doInstall;
            if (Boolean.TryParse(install, out doInstall)
                && doInstall)
            {
                installConnectionString_ = config[INSTALL_CONNECTION_STRING_KEY];
                Install();
            }
        }

        public override void Install()
        {
            using (var connection = new MySqlConnection(installConnectionString_))
            {
                try
                {
                    connection.Open();
                    // Drop and create database
                    var sql = Resources.accountability4net_sql;
                    using (var command = new MySqlCommand(sql, connection))
                    {
                        int rows = command.ExecuteNonQuery();
                    }
                    string server;
                    string database;
                    string user;
                    string password;
                    ParseConnectionString(connectionString_, out server, out database, out user, out password);

                    // Drop existing user
                    sql = String.Format("SELECT `user`, `host` FROM `mysql`.`user` WHERE `user` = @user");
                    using(var command = new MySqlCommand(sql, connection))
                    {
                        MySqlParameter userParameter = new MySqlParameter("@user", user);
                        command.Parameters.Add(userParameter);
                        sql = "DROP USER";
                        bool foundUsers = false;
                        using (var reader = command.ExecuteReader())
                        {
                            while (reader.Read())
                            {
                                var hostUser = reader.GetString("USER");
                                var host = reader.GetString("HOST");
                                sql += String.Format(" '{0}'@'{1}';", hostUser, host);
                                foundUsers = true;
                            }
                        }

                        if (foundUsers)
                        {
                            using (var command2 = new MySqlCommand(sql, connection))
                            {
                                command2.ExecuteNonQuery();
                            }
                        }
                    }
                    // Grant user access to the database
                    sql = String.Format("CREATE USER '{0}'@'%' IDENTIFIED BY '{1}'"
                        , user, password);
                    using (var command = new MySqlCommand(sql, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                        sql = String.Format("GRANT SELECT, INSERT, UPDATE, DELETE ON `{0}`.* TO '{1}'@'%'"
                            , database, user);
                    using(var command = new MySqlCommand(sql, connection))
                    {
                        command.ExecuteNonQuery();
                    }
                }
                finally
                {
                    connection.Close();
                }
            }
        }

        private void ParseConnectionString(string connectionString, out string server, out string database, out string user, out string password)
        {
            server = null;
            database = null;
            user = null;
            password = null;
            string[] pairs = connectionString.Split(';');
            foreach (string pair in pairs)
            {
                string[] parts = pair.Split('=');
                if (parts.Length < 2)
                {
                    continue;
                }
                string key = parts[0];
                string value = parts[1];
                if (key.Equals("server", StringComparison.OrdinalIgnoreCase))
                {
                    server = value;
                }
                if (key.Equals("database", StringComparison.OrdinalIgnoreCase))
                {
                    database = value;
                }
                if (key.Equals("user", StringComparison.OrdinalIgnoreCase))
                {
                    user = value;
                }
                if (key.Equals("password", StringComparison.OrdinalIgnoreCase))
                {
                    password = value;
                }
            }
        }

        private MySqlConnection connection_;
        internal MySqlConnection Connection
        {
            get
            {
                if (connection_ == null)
                {
                    connection_ = new MySqlConnection(connectionString_);
                }
                if (connection_.State != ConnectionState.Open)
                {
                    connection_.Open();
                }
                return connection_;
            }
        }

        public override IAccount CreateAccount(int number, AccountType type)
        {
            return MySQLDbAccount.Create(this, number, type);
        }

        public override IAccount GetAccount(int number)
        {
            return MySQLDbAccount.Retrieve(this, number);
        }

        public override IList<IAccount> GetAccounts(int from, int limit)
        {
            return MySQLDbAccount.Retrieve(this, from, limit);
        }

        public override IAction CreateAction(ActionType type, IAccount account, decimal amount)
        {
            return MySQLDbAction.Create(this, type, account, amount);
        }

        public override ITransaction CreateTransaction(DateTimeOffset timestamp)
        {
            return MySQLDbTransaction.Create(this, timestamp);
        }

        public override IList<ITransaction> GetTransactions()
        {
            return MySQLDbTransaction.Retrieve(this);
        }

        public override IList<ITransaction> GetTransactions(IAccount account)
        {
            return MySQLDbTransaction.Retrieve(this, account);
        }

        public override IList<ITransaction> GetTransactions(IAccount account, DateTimeOffset? after)
        {
            return MySQLDbTransaction.Retrieve(this, account, after);
        }

        public override IList<ITransaction> GetTransactions(IAccount account, DateTimeOffset? after, DateTimeOffset? before)
        {
            return MySQLDbTransaction.Retrieve(this, account, after, before);
        }

        public override void Dispose()
        {
            if (connection_ != null
                && connection_.State != ConnectionState.Closed)
            {
                try
                {
                    connection_.Close();
                }
                finally
                {
                    connection_.Dispose();
                    connection_ = null;
                }
            }
        }

        public string GetString(MySqlDataReader reader, string column)
        {
            int field = reader.GetOrdinal(column);
            if (reader.IsDBNull(field))
            {
                return null;
            }
            else
            {
                return reader.GetString(field);
            }
        }

        public override IContact CreateContact()
        {
            return MySQLDbContact.Create(this);
        }

        public override IContact GetContact(Guid id)
        {
            return MySQLDbContact.Retrieve(this, id);
        }
    }
}
